package com.green.common.util;

import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;


/**
 * 拼装sql
 * @author hxy
 *
 */
public class CreateSqlUtils {
	
	private static final Logger logger = Logger.getLogger(CreateSqlUtils.class);
	private static final String ORALCE_KEEP_STRING= " _-";
	/**
     * 过滤条件拼装方法
     * 
     * @param filterParams
     * @param filterSql
     */
    public static void createFilterSql(String filterParams, StringBuffer sql){
        if (!StringUtils.isEmpty(filterParams)){
        	if(filterParams.contains(" in")){ //支持in
        		String[] filter = filterParams.split(":");
        		for (String string : filter){
        			if(StringUtils.isNotEmpty(string) && string.contains(" in")){
        				sql.append(" And ");
                    	sql.append(string);
        			}else if(StringUtil.isNotEmpty(string)){
        				string = string.replaceAll(",", "");
        				String[] searchParas = splitSearchPara(string);
                        if (StringUtils.isEmpty(searchParas[2]) || "null".equals(searchParas[2].toLowerCase())){
                           continue; 
                        }
                        sql.append(" And ");
                        sql.append(searchParas[0]);
                        sql.append(" " + ("~~".equals(searchParas[1])||"~".equals(searchParas[1]) ? "like" : searchParas[1]));
                        if (searchParas[2].startsWith("|T|")){
                            sql.append("to_date('" + searchParas[2].substring(3) + "','yyyy-MM-dd HH24:mi:ss')");
                        }else if("~~".equals(searchParas[1])){
                        	sql.append(" '%" + searchParas[2] + "%'");
                        }else if("~".equals(searchParas[1])){
                        	sql.append(" '" +searchParas[2] + "%'");
                        }
                        else{
                            sql.append(" '" + searchParas[2] + "'");
                        }
        			}
        		}
        	}else{
        		String[] filter = filterParams.split(",");
                for (String string : filter){
                	if(StringUtils.isEmpty(string) || string.startsWith("otherParams")){
                		continue;
                	}
                	if (string.startsWith("@")){ // 特殊情况的拼装 直接相加
                    	sql.append(" "+string.substring(string.indexOf("@")+1));
                    }else if (string.startsWith("#")){ // 支持 Contains
                        String[] searchParas = splitSearchPara(string);
                        if(StringUtils.isNotEmpty(searchParas[2])){
    	                    sql.append(" And ");
    	                    sql.append("Contains(").append(searchParas[0].substring(searchParas[0].indexOf("#") + 1));
    	                    sql.append(", '");
    	                    sql.append(CreateSqlUtils.deleteOutOracleKeepString(searchParas[2]));
    	                    sql.append("') > 0 ") ;
                        }
                    }else{
                        String[] searchParas = splitSearchPara(string);
                        if (StringUtils.isEmpty(searchParas[2]) || "null".equals(searchParas[2].toLowerCase())){
                           continue; 
                        }
                        sql.append(" And ");
                        sql.append(searchParas[0]);
                        sql.append(" " + ("~~".equals(searchParas[1])||"~".equals(searchParas[1]) ? "like" : searchParas[1]));
                        if (searchParas[2].startsWith("|T|")){
                            sql.append("to_date('" + searchParas[2].substring(3) + "','yyyy-MM-dd HH24:mi:ss')");
                        }else if("~~".equals(searchParas[1])){
                        	sql.append(" '%" + searchParas[2] + "%'");
                        }else if("~".equals(searchParas[1])){
                        	sql.append(" '" +searchParas[2] + "%'");
                        }
                        else{
                            sql.append(" '" + searchParas[2] + "'");
                        }
                    }
                }
        	}
        }
    }
    public static Map<String, Object> createFilterSqlParams(String filterParams){
    	Map<String, Object> params=new HashMap<String, Object>();
    	  if (!StringUtils.isEmpty(filterParams)){
              String[] filter = filterParams.split(",");
              for (String string : filter){
            	  if(string.indexOf("|||")>0){
            		  //这里用做构建SQLID 后面的用IN 的参数,参数之间写死用|||分隔，这里做处理
            		  int index=string.indexOf("=");
            		  String value=string.substring(index+1);
            		  String[] str=value.split("\\|\\|\\|");
            		  StringBuilder buf=new StringBuilder();
            		  for(String s:str){
            			  buf.append("'").append(s).append("',");
            		  }
            		  if(buf.length()>0){
            			  params.put(string.substring(0, index), buf.substring(0,buf.length()-1));
            		  }   		  
            	  }else{
            		  String[] searchParas= splitSearchPara(string);
            		  params.put(searchParas[0], searchParas[2]);
            	  }
              }
          }
    	return params;
    }
    /**
     * 分隔一个搜索条件参数：“字段名称操作符条件值”格式 操作符支持：=等于，==等于，>大于，>=大于等于，<小于，<=小于等于，!=不等于，~~相似（即like）
     * 
     * @param searchPara
     *            String
     * @return String[]
     */
    public static String[] splitSearchPara(String searchPara)
    {
        if (logger.isDebugEnabled())
        {
            logger.debug("splitSearchPara(" + searchPara + ")") ;
        }
        String[] result = { "", "", "" };
		int pos = 0;
		for (int i = 0; i < searchPara.length(); i++)
        {
            char ch = searchPara.charAt(i) ;
            if (pos == 0
                    && (ch == '=' || ch == '>' || ch == '<' || ch == '!' || ch == '~'))
            {
                // 开始出现操作符号了，
                pos = 1 ;
            } else if (pos == 1 && (ch != '=' && ch != '~'))
            {
                // 操作符号结束了
                pos = 2 ;
            }
            result[pos] = result[pos] + ch ;
        }
        if (logger.isDebugEnabled())
        {
            logger.debug("result[" + result[0] + "],[" + result[1] + "],["
                    + result[2] + "]") ;
        }
        return result ;
    }
    
    
    public static StringBuffer createSortSql(String sortParams, StringBuffer sortSql, String defaultSortId)
    {
       StringBuffer sb=new StringBuffer();
    	
        if (StringUtils.isNotEmpty(sortParams))
        {
        	sb.append(" order by  ");

            String[] sort = sortParams.split(",");
            boolean isRamdon  = false;
            for (int i = 0; i < sort.length; i++)
            {
                if (sort[i].startsWith("+"))
                {
                    if (i != 0)
                    {
                    	sb.append(" , ");
                    }
                    sb.append(sort[i].substring(1)).append(" ASC  ");

                }
                else if (sort[i].startsWith("-"))
                {
                    if (i != 0)
                    {
                    	sb.append(" , ");
                    }
                    sb.append(sort[i].substring(1)).append(" DESC  ");

                }
                else if (sort[i].startsWith("*"))
                {

                	sb.delete(0, sb.length());
                	sb.append(" order by dbms_random.value ");
                    isRamdon = true;
                    break;
                }
            }
            if (!isRamdon && StringUtils.isNotEmpty(defaultSortId))
            {
            	sb.append(" , ").append(defaultSortId + " ");
            }  
            sortSql.append(sb);
        }
        
        return sortSql;
    }
    /**
     * 转义字符串中的oracle保留字符
     * 
     * @return
     */
    public static String deleteOutOracleKeepString(String str)
    {

        logger.debug("去掉关键字中的oracle全文检索保留字符");
        String keepString = CreateSqlUtils.ORALCE_KEEP_STRING;
        if (str == null || "".equals(str))
        {
            return str;
        }
        for (int i = 0; i < keepString.length(); i++)
        {
            if (str.indexOf(keepString.charAt(i)) != -1 && !String.valueOf(keepString.charAt(i)).equals(str))
            {
                str = StringUtils.replace(str, String.valueOf(keepString.charAt(i)), "");
            }
        }
        return str;
    }
}
